﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using WF_QLThuVien.Entities;
using WF_QLThuVien.Entities.Sach;
using WF_QLThuVien.Entities.DauSach;
using WF_QLThuVien.Utilities;
using System.Data;
using System.Data.OleDb;
using System.Windows.Forms;
using WF_QLThuVien.BusinessLayer.QuyDinh;
using WF_QLThuVien.Entities.QuyDinh;

namespace WF_QLThuVien.DataLayer.DauSach
{
    class CDAL_Sach : CConnection
    {
        DataAccessHelper db = new DataAccessHelper();
        CQuyDinh GQuyDinh;
        CBLL_QuyDinh GQuyDinhBLL = new CBLL_QuyDinh();
        public bool Insert(CSach s)
        {
            try
            {
                string cmd = "INSERT INTO Sach VALUES ('" + s.SachID + "','" + s.DauSachID + "','" + s.TrangThai + "','" + s.GhiChu + "')";
                db.ExecuteNonQuery(cmd);
                return true;
            }
            catch (Exception ex)
            {
                throw new Exception("Lỗi CDAL_Sach.Insert \n" + ex.ToString());
            }
        }

        public bool DeleteBySachID(string id)
        {
            try
            {
                string cmd = "SELECT * FROM Sach WHERE SachID='" + id + "'";
                //string tt = (string)db.GetValue(cmd).ToString();
                DataTable dt = db.GetDataTable(cmd);
                CSach s = new CSach(dt.Rows[0]); 

               // MessageBox.Show((tt.Equals(@"Duoc muon").ToString() + tt.Equals("Không Duoc muon").ToString() + (tt.Equals("Duoc muon") || tt.Equals("Không Duoc muon")).ToString()));

                if (s.TrangThai.Equals(@"Duoc muon") || s.TrangThai.Equals(@"Khong duoc muon"))
                {
                    cmd = "DELETE FROM Sach WHERE SachID ='" + id + "'";
                    db.ExecuteNonQuery(cmd);
                    return true;
                }
                return false;
            }
            catch (Exception ex)
            {
                throw new Exception("Lỗi CDAL_Sach.DeleteBySachID \n" + ex.ToString());
            }
        }

        public bool DeleteByDauSachID(string id)
        {
            try
            {
                List<CSach> lst = GetListByDauSachID(id);
                bool flag = false;
                foreach (CSach s in lst)
                {
                    if (s.TrangThai.Equals(@"Duoc muon") || s.TrangThai.Equals(@"Khong duoc muon"))
                    {
                        flag = true;
                        break;
                    }
                }               

                if(flag)
                {
                    foreach (CSach s in lst)
                    {
                        DeleteBySachID(s.SachID);
                    }
                    return true;
                }
                return false;
            }
            catch (Exception ex)
            {
                throw new Exception("Lỗi CDAL_Sach.DeleteByDauSachID \n" + ex.ToString());
            }
        }

        public bool UpdateBySachID(CSach s)
        {
            try
            {
                string cmd = "UPDATE Sach SET TrangThai=N'" + s.TrangThai + "', GhiChu=N'" + s.GhiChu + "' WHERE SachID='" + s.SachID + "'";
                db.ExecuteNonQuery(cmd);
                return true;
            }
            catch (Exception ex)
            {
                throw new Exception("Lỗi CDAL_Sach.UpdateBySachID \n" + ex.ToString());
            }
        }

        public bool UpdateByDauSachID(CSach s)
        {
            try
            {
                string cmd = "UPDATE Sach SET TrangThai='" + s.TrangThai + "' WHERE DauSachID='" + s.DauSachID + "'";
                db.ExecuteNonQuery(cmd);
                return true;
            }
            catch (Exception ex)
            {
                throw new Exception("Lỗi CDAL_Sach.UpdateByDauSachID \n" + ex.ToString());
            }
        }

        public bool UpdateByDauSachID(CDauSach ds)
        {
            try
            {
                string cmd = "UPDATE Sach SET TrangThai='" + ds.TrangThai + "' WHERE DauSachID='" + ds.MaDauSach + "'";
                db.ExecuteNonQuery(cmd);
                return true;
            }
            catch (Exception ex)
            {
                throw new Exception("Lỗi CDAL_Sach.UpdateByDauSachID \n" + ex.ToString());
            }
        }

        private List<CSach> GetList(string cmd)
        {
            DataTable dt = db.GetDataTable(cmd);
            List<CSach> lst = new List<CSach>();
            foreach (DataRow r in dt.Rows)
            {
                lst.Add(new CSach(r));
            }
            return lst;
        }

        public List<CSach> GetListAll()
        {
            try
            {
                string cmd = "SELECT * FROM Sach";
                return GetList(cmd);
            }
            catch (Exception ex)
            {
                throw new Exception("Lỗi CDAL_Sach.DetListAll \n" + ex.ToString());
            }
        }

        public CSach GetSachBySachID(string id)
        {
            try
            {
                string cmd = "SELECT * FROM Sach WHERE SachID='" + id + "'";
                CSach s = (CSach)db.GetValue(cmd);
                return s;
            }
            catch (Exception ex)
            {
                throw new Exception("Lỗi CDAL_Sach.GetSachBySachID \n" + ex.ToString());
            }
        }

        public List<CSach> GetListByDauSachID(string id)
        {
            try
            {
                string cmd = "SELECT * FROM Sach WHERE DauSachID='" + id + "'";
                return GetList(cmd);
            }
            catch (Exception ex)
            {
                throw new Exception("Lỗi CDAL_Sach.GetListByDauSachID \n" + ex.ToString());
            }
        }

        public List<CSach> GetListByTenSach(string name)
        {
            try
            {
                string cmd = "SELECT s.SachID, s.DauSachID, s.TrangThai, s.GhiChu FROM Sach s, DauSach ds WHERE ds.DauSachID=s.DauSachID and ds.TenDauSach=N'" + name + "'";
                return GetList(cmd);
            }
            catch (Exception ex)
            {
                throw new Exception("Lỗi CDAL_Sach.GetListByTenSach \n" + ex.ToString());
            }
        }

        public List<CSach> GetListByTacGia(string name)
        {
            try
            {
                string cmd = "SELECT s.SachID, s.DauSachID, s.TrangThai, s.GhiChu FROM Sach s, DauSach ds WHERE ds.DauSachID=s.DauSachID and ds.TacGia=N'" + name + "'";
                return GetList(cmd);
            }
            catch (Exception ex)
            {
                throw new Exception("Lỗi CDAL_Sach.GetListByTacGia \n" + ex.ToString());
            }
        }

        public List<CSach> GetListByNgayNhap(DateTime t)
        {
            try
            {
                string cmd = "SELECT s.SachID, s.DauSachID, s.TrangThai, s.GhiChu FROM Sach s, DauSach ds WHERE ds.DauSachID=s.DauSachID and ds.NgayNhap=#" + t + "#";
                return GetList(cmd);
            }
            catch (Exception ex)
            {
                throw new Exception("Lỗi CDAL_Sach.GetListByNgayNhap \n" + ex.ToString());
            }
        }

        public List<CSach> GetListByNgonNgu(string name)
        {
            try
            {
                string cmd = "SELECT s.SachID, s.DauSachID, s.TrangThai, s.GhiChu FROM Sach s, DauSach ds WHERE ds.DauSachID=s.DauSachID and ds.NgonNgu=N'" + name + "'";
                return GetList(cmd);
            }
            catch (Exception ex)
            {
                throw new Exception("Lỗi CDAL_Sach.GetListByNgonNgu \n" + ex.ToString());
            }
        }

        public List<CSach> GetListByNamXB(int year)
        {
            try
            {
                string cmd = "SELECT s.SachID, s.DauSachID, s.TrangThai, s.GhiChu FROM Sach s, DauSach ds WHERE ds.DauSachID=s.DauSachID and ds.NamXB='" + year + "'";
                return GetList(cmd);
            }
            catch (Exception ex)
            {
                throw new Exception("Lỗi CDAL_Sach.GetListByNamXB \n" + ex.ToString());
            }
        }

        public List<CSach> GetListByNhaXB(string name)
        {
            try
            {
                string cmd = "SELECT s.SachID, s.DauSachID, s.TrangThai, s.GhiChu FROM Sach s, DauSach ds WHERE ds.DauSachID=s.DauSachID and ds.NhaXB=N'" + name + "'";
                return GetList(cmd);
            }
            catch (Exception ex)
            {
                throw new Exception("Lỗi CDAL_Sach.GetListByNhaXB \n" + ex.ToString());
            }
        }

        public List<CSach> GetListByTheLoai(string name)
        {
            try
            {
                string cmd = "SELECT s.SachID, s.DauSachID, s.TrangThai, s.GhiChu FROM Sach s, DauSach ds WHERE ds.DauSachID=s.DauSachID and ds.TheLoai=N'" + name + "'";
                return GetList(cmd);
            }
            catch (Exception ex)
            {
                throw new Exception("Lỗi CDAL_Sach.GetListByTheLoai \n" + ex.ToString());
            }
        }

        public List<CSach> GetListByTrangThai(string name)
        {
            try
            {
                string cmd = "SELECT s.SachID, s.DauSachID, s.TrangThai, s.GhiChu FROM Sach s, DauSach ds WHERE ds.DauSachID=s.DauSachID and ds.TrangThai=N'" + name + "'";
                return GetList(cmd);
            }
            catch (Exception ex)
            {
                throw new Exception("Lỗi CDAL_Sach.GetListByTrangThai \n" + ex.ToString());
            }
        }

        public int CountTrangThaiBySachID(string tt, string id)
        {
            try
            {
                string cmd = "SELECT COUNT(*) FROM Sach WHERE TrangThai = '%" + tt + "%' and SachID='" + id + "'";
                return (int)db.GetValue(cmd);
            }
            catch (Exception ex)
            {
                throw new Exception("Lỗi CDAL_Sach.CountTrangThai \n" + ex.ToString());
            }
        }

        public int CountTrangThaiByDauSachID(string tt, string id)
        {
            try
            {
                string cmd = "SELECT COUNT(*) FROM Sach WHERE TrangThai like '%" + tt + "%' and DauSachID='" + id + "'";
                return (int)db.GetValue(cmd);
            }
            catch (Exception ex)
            {
                throw new Exception("Lỗi CDAL_Sach.CountTrangThai \n" + ex.ToString());
            }
        }

        #region: PANDA
        OleDbCommand cmd = new OleDbCommand();
        public void KTConnect()
        {
            //kiem tra lai ket noi
            try { Connection(); }
            catch (Exception ex) { throw ex; }
        }

        public string GetTenSachByMaSach(string maSach)
        {
            KTConnect();
            try
            {
                string tenSach = "";
                //SELECT TenDauSach FROM Sach LEFT JOIN DauSach ON Sach.DauSachID=DauSach.DauSachID
                cmd = new OleDbCommand("SELECT TenDauSach FROM Sach LEFT JOIN DauSach ON Sach.DauSachID=DauSach.DauSachID WHERE Sach.SachID='" + maSach + "'", dataConnection);
                //tenSach = (string)cmd.ExecuteScalar();
                OleDbDataReader dataReader = cmd.ExecuteReader();
                if (dataReader == null)
                {
                    throw new Exception("Không Có Dữ Liệu - CDAL_Sach");
                }
                else
                {
                    while (dataReader.Read())
                    {
                        tenSach = dataReader.GetValue(0).ToString();
                    }
                }
                return tenSach;
            }
            catch (System.Exception ex)
            {
                throw new Exception("Không Tìm Thấy Tên Sách\n" + ex.ToString());
            }
        }

        public CSach GetSachByMaSach(string maSach)
        {
            KTConnect();
            try
            {
                CSach s = new CSach();
                //m_dauSachID = "";
                //m_ghiChu = "";
                //m_sachID = "";
                //m_trangThai = "";
                //Thông tin quyển sách
                //SELECT TenDauSach FROM Sach LEFT JOIN DauSach ON Sach.DauSachID=DauSach.DauSachID
                cmd = new OleDbCommand("SELECT * FROM Sach WHERE SachID='" + maSach + "'", dataConnection);
                OleDbDataReader dataReader = cmd.ExecuteReader();
                if (dataReader == null)
                {
                    throw new Exception("Không Tìm Thấy Sách - CDAL_Sach");
                }
                else
                {
                    while (dataReader.Read())
                    {
                        s.SachID = dataReader.GetValue(0).ToString();
                        s.DauSachID = dataReader.GetValue(1).ToString();
                        s.TrangThai = dataReader.GetValue(2).ToString();
                        s.GhiChu = dataReader.GetValue(3).ToString();
                    }
                }
                return s;
            }
            catch (System.Exception ex)
            {
                throw new Exception("Không Tìm Thấy Sách - CDAL_Sach\n" + ex.ToString());
            }
        }

        public bool UpdateTTDangMuon(string maSach)
        {
            KTConnect();
            try
            {
                //Kiểm tra các trạng thái không được Update
                cmd = new OleDbCommand("SELECT TrangThai FROM Sach WHERE SachID='" + maSach + "'", dataConnection);
                string trangThai = (string)cmd.ExecuteScalar();
                if (trangThai.Equals(@"Duoc muon"))
                {
                    //Update Trạng Thái Thành Đang Mượn
                    cmd = new OleDbCommand("UPDATE Sach SET TrangThai='Dang muon' WHERE SachID='" + maSach + "'", dataConnection);
                    cmd.ExecuteNonQuery();
                    return true;
                }
                else
                {
                    throw new Exception("Không Thể Cho Mượn Sách Vì: " + trangThai.ToString());
                }
            }
            catch (System.Exception ex)
            {
                throw new Exception("Lỗi Update Trạng Thái Đang Mượn Sách - CDAL_Sach\n" + ex.ToString());
            }
        }

        public bool UpdateTTMatSach(string maSach)
        {
            KTConnect();
            try
            {
                //Kiểm tra các trạng thái không được Update
                cmd = new OleDbCommand("SELECT TrangThai FROM Sach WHERE SachID='" + maSach + "'", dataConnection);
                string trangThai = (string)cmd.ExecuteScalar();
                if (trangThai.Equals(@"Dang muon"))
                {
                    //Update Trạng Thái CTPM Thành Mất
                    cmd = new OleDbCommand("UPDATE ChiTietPhieuMuon SET TrangThai='Mat' WHERE SachID='" + maSach + "'", dataConnection);
                    cmd.ExecuteNonQuery();
                    //Update Trạng Thái Sách
                    cmd = new OleDbCommand("UPDATE Sach SET TrangThai='Mat' WHERE SachID='" + maSach + "'", dataConnection);
                    cmd.ExecuteNonQuery();
                    return true;
                }
                else
                {
                    throw new Exception("Không Thể Báo Mất Vì: " + trangThai.ToString());
                }
            }
            catch (System.Exception ex)
            {
                throw new Exception("Lỗi Update Trạng Thái Mất Sách - CDAL_Sach\n" + ex.ToString());
            }
        }

        public double GiaTienMatSach(string maSach)
        {
            GQuyDinh = GQuyDinhBLL.GetQuyDinh();
            KTConnect();
            try
            {
                cmd = new OleDbCommand("SELECT GiaTien FROM DauSach, Sach WHERE DauSach.DauSachID=Sach.DauSachID AND SachID='" + maSach + "'", dataConnection);
                double giaTien = (double)cmd.ExecuteScalar();
                return giaTien * GQuyDinh.TienPhatMatSach;

            }
            catch (System.Exception ex)
            {
                throw new Exception("Lỗi Tìm Giá Tiền Phạt Mất Sách - CDAL_Sach\n " + ex.ToString());
            }
        }

        public bool UpdateTTHongSach(string maSach, string ghiChu)
        {
            KTConnect();
            try
            {
                //Kiểm tra các trạng thái không được Update
                cmd = new OleDbCommand("SELECT TrangThai FROM Sach WHERE SachID='" + maSach + "'", dataConnection);
                string trangThai = (string)cmd.ExecuteScalar();
                if (trangThai.ToString().Equals(@"Dang muon"))
                {
                    //Update CTPM liên quan nếu trạng thái đó là đang mượn. Vì lỗi do đọc giả làm hỏng!
                    cmd = new OleDbCommand("UPDATE ChiTietPhieuMuon SET TrangThai='Hong' WHERE SachID='" + maSach + "'", dataConnection);
                    cmd.ExecuteNonQuery();
                    //Update Sách
                    cmd = new OleDbCommand("UPDATE Sach SET TrangThai='Hong', GhiChu='" + ghiChu + "' WHERE SachID='" + maSach + "'", dataConnection);
                    cmd.ExecuteNonQuery();
                    return true;
                }
                else if (trangThai.Equals(@"Khong duoc muon") || trangThai.Equals(@"Duoc muon"))
                {
                    //Update Trạng Thái Thành Hỏng - Lỗi do thư viện hỏng
                    cmd = new OleDbCommand("UPDATE Sach SET TrangThai='Hong', GhiChu='" + ghiChu + "' WHERE SachID='" + maSach + "'", dataConnection);
                    cmd.ExecuteNonQuery();
                    return true;
                }
                else
                {
                    throw new Exception("Không Thể Báo Hỏng Vì Trạng Thái: " + trangThai.ToString());
                }
            }
            catch (System.Exception ex)
            {
                throw new Exception("Lỗi Update Trạng Thái Hỏng Sách - CDAL_Sach\n" + ex.ToString());
            }
        }

        public bool UpdateTTTraSach(string maSach)
        {
            KTConnect();
            try
            {
                //Kiểm tra các trạng thái không được Update
                cmd = new OleDbCommand("SELECT TrangThai FROM Sach WHERE SachID='" + maSach + "'", dataConnection);
                string trangThai = (string)cmd.ExecuteScalar();
                if (trangThai.Equals(@"Dang muon"))
                {
                    DateTime now = DateTime.Now;
                    //Update Trạng Thái Chi Tiết Phiếu Mượn Thành Đã Trả, Ngày Trả = Ngày Hiện Tại
                    cmd = new OleDbCommand("UPDATE ChiTietPhieuMuon SET TrangThai='Da tra', NgayTra=#" + now + "#" + " WHERE SachID='" + maSach + "'", dataConnection);
                    cmd.ExecuteNonQuery();
                    //Update Sách Trở Về Được Mượn
                    cmd = new OleDbCommand("UPDATE Sach SET TrangThai='Duoc muon' WHERE SachID='" + maSach + "'", dataConnection);
                    cmd.ExecuteNonQuery();

                    return true;
                }
                else
                {
                    throw new Exception("Không Thể Trả Vì: " + trangThai.ToString());
                }
            }
            catch (System.Exception ex)
            {
                throw new Exception("Lỗi Update Trạng Thái Trả Sách - CDAL_Sach\n" + ex.ToString());
            }
        }

        #endregion
    }
}
